﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using WL_Store.Model;

namespace WL_Store.DAL
{
   public  class SystemLogsDAL
    {
        /// <summary>
        /// 查询所有修改日志
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        public List<SystemLogsModel> GetSystems(SystemLogsSearchModel searchModel)
        {
            string sql = @"select q.Id,q.name,a.Grade,a.GradeId,q.[count],q.modificationTime from 
                            (select s.Id,e.name,e.GradeId,s.[count],s.modificationTime from systemLogs as s inner join AdminInfo as e on s.number=e.number) 
                            as q inner join Grades as a on q.GradeId=a.GradeId";
            //构造where的泛型集合
            List<string> listWhere = new List<string>();
            //构造参数化查询数组的泛型集合
            List<SqlParameter> listParameters = new List<SqlParameter>();

            if (!string.IsNullOrWhiteSpace(searchModel.name))
            {
                listWhere.Add("q.name=@name");
                listParameters.Add(new SqlParameter("@name", searchModel.name));
            }
            if (searchModel.StartTime.HasValue)
            {
                listWhere.Add("q.modificationTime>=@StartTime");
                listParameters.Add(new SqlParameter("@StartTime", searchModel.StartTime));
            }
            if (searchModel.EndTime.HasValue)
            {
                listWhere.Add("q.modificationTime<=@EndTime");
                listParameters.Add(new SqlParameter("@EndTime", searchModel.EndTime));
            }
            if (searchModel.GradeId>0)
            {
                listWhere.Add("a.GradeId=@GradeId");
                listParameters.Add(new SqlParameter("@GradeId", searchModel.GradeId));
            }
            if (listWhere.Count>0)
            {
                sql += " where " + string.Join(" and " , listWhere.ToArray());
            }

            SqlDataReader reader = Dbhelper.ExecuteReader(sql,listParameters.ToArray());

            List<SystemLogsModel> listmodels = new List<SystemLogsModel>();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    SystemLogsModel model = new SystemLogsModel();
                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.name = reader["name"].ToString();
                    model.Grade = reader["Grade"].ToString();
                    model.count = reader["count"].ToString();
                    model.modificationTime = Convert.ToDateTime(reader["modificationTime"]);
                    listmodels.Add(model);
                }
                reader.Close();
            }
            return listmodels;
        }

        /// <summary>
        /// 查询管理权限
        /// </summary>
        /// <returns></returns>
        public DataTable PermissionInfo()
        {
            string sql = "select*from Grades";
            return Dbhelper.ExecuteTable(sql);
        }

    }
}
